



ALTER           proc sp_products_list_having_customer_part_numbers
@customer_id  int
as
begin
	declare @results Table(ProcessStep_ID int, IST_Manufacturing_Part_Number varchar (20), IST_Marketing_Part_Number varchar (20), Product_ID int, P_Type varchar (10), P_Comments varchar (200),Customer_PartNumber varchar(200) )
	declare @proc_id varchar (20)
	declare @mft_part_no varchar (20)
	declare @mkt_part_no varchar (20)
	declare @prod_id int
	declare @p_type varchar (10)
	declare @p_description varchar (200)
	declare @customer_partnumber varchar(200)

	insert into @results values(null,'---- CHIPS -----','---- CHIPS -----',null,null,null,null)
	declare ncursor cursor for

	select ProcessStep_ID, Part_Number , P_Marketing_Part_Number, p.Product_id  ,P_Type, P_Comments ,Customer_PartNumber 
	from Product p,Customer_PartNumbers c
	where p.product_id = c.product_id and c.customer_id = @customer_id and p_type is NULL

	open ncursor
		fetch next from ncursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type, @p_description ,@customer_partnumber
		while @@fetch_status = 0
		begin
			insert into @results values(@proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description,@customer_partnumber)
			set @proc_id = null
			set @mft_part_no = null
			set @mkt_part_no = null
			set @prod_id = null
			set @p_type = null
			set @p_description = null
			set @customer_partnumber = null
			fetch next from ncursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type, @p_description ,@customer_partnumber
		end
	close ncursor
	deallocate ncursor
	insert into @results values(null,'---- BOARD -----','---- BOARD -----',null,null,null,null)
	declare bcursor cursor for

	select ProcessStep_ID, Part_Number , P_Marketing_Part_Number, p.Product_id  ,P_Type, P_Comments ,Customer_PartNumber 
	from Product p,Customer_PartNumbers c
	where p.product_id = c.product_id and c.customer_id = @customer_id and P_Type ='BOARD'

	open bcursor
		
		fetch next from bcursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description ,@customer_partnumber
		while @@fetch_status = 0
		begin
			insert into @results values(@proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description,@customer_partnumber)
			set @proc_id = null
			set @mft_part_no = null
			set @mkt_part_no = null
			set @prod_id = null
			set @p_type = null
			set @p_description = null
			set @customer_partnumber = null
			fetch next from bcursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description ,@customer_partnumber
		end
	close bcursor
	deallocate bcursor
	insert into @results values(null,'---- KIT -----','---- KIT -----',null,null,null,null)
	declare kcursor cursor for

	select ProcessStep_ID, Part_Number , P_Marketing_Part_Number, p.Product_id  ,P_Type, P_Comments ,Customer_PartNumber 
	from Product p,Customer_PartNumbers c
	where p.product_id = c.product_id and c.customer_id = @customer_id and P_Type ='KIT'

	open kcursor
		fetch next from kcursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description ,@customer_partnumber
		while @@fetch_status = 0
		begin
			insert into @results values(@proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description,@customer_partnumber )
			set @proc_id = null
			set @mft_part_no = null
			set @mkt_part_no = null
			set @prod_id = null
			set @p_type = null
			set @p_description = null
			set @customer_partnumber = null
			fetch next from kcursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description ,@customer_partnumber
		end
	close kcursor
	deallocate kcursor
end
select * from @results   






